package com.bigdata.team2.finalproject;

import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class WebController {
    private org.slf4j.Logger logger = LoggerFactory.getLogger(this.getClass());

    private final
    JdbcTemplate jdbcTemplate;

    @Autowired
    public WebController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    @RequestMapping("/getAllNeedExpand")
    public List<Map<String, Object>> getAllNeedExpand(){
        String sql = "SELECT t.city, if(d.countOlt is NULL ,0,d.countOlt) countOlt,if(f.countSw is NULL ,0,f.countSw) countSW FROM t_citylist t " +
                " left JOIN  (SELECT c.city_name, count(c.ip) countOlt from ( " +
                " SELECT  DISTINCT b.city_name, a.ip FROM OLT_NeedToExpand a " +
                " JOIN  olt_info_all b " +
                "  ON b.olt_ip = a.ip) c " +
                " GROUP BY c.city_name) d" +
                " ON t.city = d.city_name" +
                "  LEFT JOIN (SELECT e.city ,count(DISTINCT A_device) countSw FROM t_93_need_expand e GROUP BY e.city) f" +
                "  ON t.city = f.city" +
                " ORDER BY t.city;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }



    @RequestMapping("/getCityDetail")
    public Map<String, Object> getCityDetail(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT all_user, all_act_user, all_itv, all_sw, all_olt FROM display_city_info WHERE city='"+cityName+"'";
        logger.info(sql);
        return jdbcTemplate.queryForMap(sql);
    }


    @RequestMapping("/getSWNeedExpandNumByCity")
    public List<Map<String, Object>> getSWNeedExpandNumByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT '95' utilization, count(A_device) countSW FROM t_93_need_expand_real WHERE max_utilization>=0.95 AND city = '"+cityName+"' " +
                "UNION " +
                "SELECT '90', count(A_device) FROM t_93_need_expand_real WHERE max_utilization>=0.9 AND max_utilization<0.95 AND city =' "+cityName+"' " +
                "UNION " +
                "SELECT '80', count(A_device) FROM t_93_need_expand_real WHERE max_utilization>=0.8 AND max_utilization<0.9 AND city = '"+cityName+"' " +
                "UNION " +
                "SELECT '70', count(A_device) FROM t_93_need_expand_real WHERE max_utilization>=0.7 AND max_utilization<0.8 AND city = '"+cityName+"' " +
                "UNION " +
                "SELECT '60', count(A_device) FROM t_93_need_expand_real WHERE max_utilization>=0.6 AND max_utilization<0.7 AND city = '"+cityName+"' " +
                "UNION " +
                "SELECT 'etc', countAll - countNeed FROM " +
                "  (SELECT count(city) countAll FROM sw_quansheng WHERE city = '"+cityName+"') a, " +
                "  (SELECT count(A_device) countNeed FROM t_93_need_expand_real WHERE max_utilization>=0.6 AND city = '"+cityName+"') b";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getOLTNeedExpandNumByCity")
    public List<Map<String, Object>> getOLTNeedExpandNumByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT '95' utilization, count(olt_ip) countOLT FROM olt_info_all WHERE max_flow>=0.95 AND city_name = '"+cityName+"'" +
                " UNION   " +
                " SELECT '90', count(olt_ip) countOLT FROM olt_info_all WHERE max_flow>=0.9 AND max_flow<0.95 AND city_name = '"+cityName+"'" +
                " UNION   " +
                " SELECT '80', count(olt_ip) countOLT FROM olt_info_all WHERE max_flow>=0.8 AND max_flow<0.9 AND city_name = '"+cityName+"'" +
                " UNION   " +
                " SELECT '70', count(olt_ip) countOLT FROM olt_info_all WHERE max_flow>=0.7 AND max_flow<0.8 AND city_name = '"+cityName+"'" +
                " UNION   " +
                " SELECT '60', count(olt_ip) countOLT FROM olt_info_all WHERE max_flow>=0.6 AND max_flow<0.7 AND city_name = '"+cityName+"'" +
                " UNION   " +
                " SELECT 'etc', count(olt_ip) countOLT FROM olt_info_all WHERE max_flow<0.6 AND city_name = '"+cityName+"'";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }



    @RequestMapping("/getSWListByCityNameAndUtilization")
    public List<Map<String, Object>> getSWListByCityNameAndUtilization(
            @RequestParam(value = "city_name") String cityName,
            @RequestParam(value = "option") String utilization
    ){
        String sql = "";
        switch (utilization){
            case "95":
                sql = "SELECT a.city, A_device sw_name, a.max_utilization, Loopback_sw   FROM t_93_need_expand_real a " +
                        "LEFT JOIN sw_quansheng b " +
                        "ON a.A_device = b.sw_name " +
                        "WHERE a.max_utilization>=0.95 AND a.city = '"+cityName+"'";
                break;
            case "90":
                sql = "SELECT a.city, A_device sw_name, a.max_utilization, Loopback_sw   FROM t_93_need_expand_real a " +
                        "LEFT JOIN sw_quansheng b " +
                        "ON a.A_device = b.sw_name " +
                        "WHERE a.max_utilization>=0.90 AND a.max_utilization<0.95 AND a.city = '"+cityName+"'";
                break;
            case "80":
                sql = "SELECT a.city, A_device sw_name, a.max_utilization, Loopback_sw   FROM t_93_need_expand_real a " +
                        "LEFT JOIN sw_quansheng b " +
                        "ON a.A_device = b.sw_name " +
                        "WHERE a.max_utilization>=0.80 AND a.max_utilization<0.90 AND a.city = '"+cityName+"'";
                break;
            case "70":
                sql = "SELECT a.city, A_device sw_name, a.max_utilization, Loopback_sw   FROM t_93_need_expand_real a " +
                        "LEFT JOIN sw_quansheng b " +
                        "ON a.A_device = b.sw_name " +
                        "WHERE a.max_utilization>=0.70 AND a.max_utilization<0.80 AND a.city = '"+cityName+"'";
                break;
            case "60":
                sql = "SELECT a.city, A_device sw_name, a.max_utilization, Loopback_sw   FROM t_93_need_expand_real a " +
                        "LEFT JOIN sw_quansheng b " +
                        "ON a.A_device = b.sw_name " +
                        "WHERE a.max_utilization>=0.60 AND a.max_utilization<0.70 AND a.city = '"+cityName+"'";
                break;
            case "etc":
                sql = "SELECT a.city, a.sw_name, '<0.5' max_utilization,  Loopback_sw, sw_type  FROM sw_quansheng a " +
                        "WHERE a.sw_name NOT  IN (SELECT b.A_device FROM t_93_need_expand_real b " +
                        "WHERE b.city = '"+cityName+"') AND a.city = '"+cityName+"'";
                break;
        }
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getOLTListByCityNameAndUtilization")
    public List<Map<String, Object>> getOLTListByCityNameAndUtilization(
            @RequestParam(value = "city_name") String cityName,
            @RequestParam(value = "option") String utilization
    ){
        String sql = "";
        switch (utilization){
            case "95":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow >= 0.95 AND city_name  = '"+cityName+"'";
                break;
            case "90":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow >= 0.90 AND max_flow < 0.95 AND city_name  = '"+cityName+"'";
                break;
            case "80":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow >= 0.80 AND max_flow < 0.90 AND city_name  = '"+cityName+"'";
                break;
            case "70":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow >= 0.70 AND max_flow < 0.80 AND city_name  = '"+cityName+"'";
                break;
            case "60":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow >= 0.60 AND max_flow < 0.70 AND city_name  = '"+cityName+"'";
                break;
            case "etc":
                sql = "SELECT city_name, olt_name, olt_ip, upspeed, max_flow, act_users " +
                        "FROM olt_info_all " +
                        "WHERE max_flow < 0.60 AND city_name  = '"+cityName+"'";
                break;
        }
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }



    @RequestMapping("/getSWUplinkByName")
    public List<Map<String, Object>> getSWUplinkByName(
            @RequestParam(value = "sw_name") String swName
    ){
        String sql = "SELECT max(A_name) sw_name, device_name, max(device_type) device_type,max(bandwidth_Mbps/1000) bandWidth, " +
                "max(P_Apex_in_avg) max_flow, count(attribute) countCircuit FROM SW_UP " +
                "  JOIN t_srbsmse " +
                "  ON device_loopback = B_IP " +
                "WHERE A_name = '"+swName+"' AND A_port LIKE '%Gigabit%' " +
                "GROUP BY device_name";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getOltBySWName")
    public List<Map<String, Object>> getOltBySWName(
            @RequestParam(value = "sw_name") String swName
    ){
        String sql = "SELECT olt_name, max(upspeed) countCircuit, max(max_flow) maxFlow,max(act_users) actUser FROM " +
                "(SELECT a.olt_name, a.act_users, a.max_flow, sw_name,upspeed FROM olt_connect_sw a " +
                "JOIN olt_info_all b " +
                "  ON b.olt_ip = a.olt_ip " +
                "  WHERE sw_name = '"+swName+"' ) a " +
                "GROUP BY olt_name";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getSWNameByOltName")
    public Map<String, Object> getSWNameByOltName(
            @RequestParam(value = "olt_name") String oltName
    ){
        String sql = "SELECT DISTINCT sw_name FROM olt_connect_sw WHERE olt_name = '"+oltName+"'";
        logger.info(sql);
        return jdbcTemplate.queryForMap(sql);
    }

    @RequestMapping("/getOltNeedExpand")
    public List<Map<String, Object>> getOltNeedExpand(){
        String sql = "SELECT t.city, if(d.countOlt is NULL ,0,d.countOlt) countOlt FROM t_citylist t " +
                "left JOIN  (SELECT c.city_name, count(c.ip) countOlt from ( " +
                " SELECT  DISTINCT b.city_name, a.ip FROM OLT_NeedToExpand a " +
                " JOIN  olt_info_all b " +
                "  ON b.olt_ip = a.ip) c " +
                " GROUP BY c.city_name) d " +
                "ON t.city = d.city_name " +
                "ORDER BY countOlt";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getOltNeedExpandListByCity")
    public List<Map<String, Object>> getOltNeedExpandListByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT DISTINCT b.city_name, b.olt_name, a.ip, b.upspeed, a.maxFlow, a.act_users FROM OLT_NeedToExpand a " +
                "JOIN  olt_info_all b " +
                "  ON a.ip = b.olt_ip " +
                "WHERE b.city_name = '"+cityName+"' " +
                "ORDER BY a.maxFlow DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }



    @RequestMapping("/getSWNeedExpand")
    public List<Map<String, Object>> getSWNeedExpand(){
        String sql = "SELECT a.city, if(count(b.circuit_name) is NULL ,0,count(b.circuit_name)) countSW FROM t_citylist a " +
                "LEFT JOIN t_93_need_expand b " +
                "ON a.city = b.city " +
                "GROUP BY a.city " +
                "order by countSW";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getSWNeedExpandListByCity")
    public List<Map<String, Object>> getSWNeedExpandListByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT city, circuit_name, bandwith, A_device, utilization  FROM t_93_need_expand " +
                "WHERE city = '"+cityName+"' " +
                "ORDER BY utilization DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getEponPortExpand")
    public List<Map<String, Object>> getEponPortExpand(){
        String sql = "SELECT t.city, if(count(e.IP) is NULL ,0,count(e.IP)) countEpon FROM t_citylist t " +
                "LEFT JOIN EPON_XiaLian_LieHua e " +
                "  ON t.city = e.location " +
                "GROUP BY t.city " +
                "ORDER BY city;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @RequestMapping("/getCityBoundList")
    public List<Map<String, Object>> getCityBoundList(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT concat(bound,'M') name  ,num value  FROM result_city_bound  WHERE city = '"+cityName+"'";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }



    @RequestMapping("/getGponPortExpand")
    public List<Map<String, Object>> getGponPortExpand(){
        String sql = "SELECT t.city, if(count(g.IP) is NULL ,0,count(g.IP)) countGpon FROM t_citylist t " +
                "LEFT JOIN GPON_XiaLian_LieHua g " +
                "  ON t.city = g.location " +
                "GROUP BY t.city " +
                "ORDER BY city;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    //非直连olt的itv质量
    @RequestMapping("/getNotDirectOLTITVQuality")
    public List<Map<String, Object>> getNotDirectOLTITVQuality(){
        jdbcTemplate.update("SET @rownum =0;");
        String sql = "SELECT @rownum := @rownum + 1 as seq, a.itv_delay FROM  " +
                "  (SELECT itv_delay FROM ITV_delay_user  " +
                "    WHERE UP_Link = '否' and  itv_delay < 400 " +
                "    ORDER BY RAND() LIMIT 100) a;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }

    //直连olt的itv质量
    @RequestMapping("/getDirectOLTITVQuality")
    public List<Map<String, Object>> getDirectOLTITVQuality(){
        jdbcTemplate.update("SET @rownum =0;");
        String sql = "SELECT @rownum := @rownum + 1 as seq, a.itv_delay FROM  " +
                "  (SELECT itv_delay FROM ITV_delay_user  " +
                "    WHERE UP_Link = '是' and  itv_delay < 400 " +
                "    ORDER BY RAND() LIMIT 100) a;";
        logger.info(sql);

        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getOltDirectUplinkSRBSDetail")
    public List<Map<String, Object>> getOltDirectUplinkSRBSDetail(
            @RequestParam(value = "olt_ip") String oltIP
    ){
        String sql = "SELECT DISTINCT city_name ,olt_ip, olt_name , uplink_ip, uplink_type, b.bandWith FROM olt_direct_srbas a " +
                "LEFT JOIN " +
                "  (SELECT concat(olt_ip,'--',uplink_ip) conn , count(olt_ip) bandWith FROM olt_direct_srbas " +
                "GROUP BY concat(olt_ip,'--',uplink_ip)) b " +
                "  ON  b.conn = concat(a.olt_ip,'--',a.uplink_ip) " +
                "WHERE olt_ip = '"+oltIP+"'";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getAllDisplayInfo")
    public Map<String, Object> getAllUser(){
        String sql = "SELECT all_user, all_act_user, all_ITV, all_sw, all_olt   from display_info";
        logger.info(sql);
        return jdbcTemplate.queryForMap(sql);
    }

    
    @RequestMapping("/getOltPonUserCount")
    public List<Map<String, Object>> getOltPonUserCount(
            @RequestParam(value = "olt_name") String oltName
    ){
        String sql = "SELECT  if_position, if(max(puser) is NULL ,0, max(puser)) userCount FROM result_downall " +
                "  JOIN olt_info_all " +
                "  ON ip = olt_ip " +
                "WHERE olt_name = '"+oltName+"' " +
                "GROUP BY if_position";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getOltListCalculateOnBroadbandByCity")
    public List<Map<String, Object>> getOltCalculateOnBroadbandByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT DISTINCT(olt_name), p_in_olt, p_new_in_olt_line  " +
                "FROM  olt_sw_bandwidth_liyonglv_forecast_all a  " +
                "  JOIN olt_info_all b  " +
                "  ON b.olt_ip = a.olt_ip  " +
                " WHERE p_new_in_olt_line>=0.6 AND a.city = '"+cityName.substring(0,2)+"'" +
                " ORDER BY p_new_in_olt_line DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getSWListCalculateOnBroadbandByCity")
    public List<Map<String, Object>> getSWListCalculateOnBroadbandByCity    (
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT A_name, avg(p_in_sw) p_in_sw, avg(p_new_in_sw_line) p_new_in_sw_line  " +
                "FROM olt_sw_bandwidth_liyonglv_forecast_all a  " +
                "  JOIN SW_UP  " +
                "  ON sw_ip = A_IP  " +
                " WHERE p_new_in_sw_line >= 0.6 AND  a.city = '"+cityName.substring(0,2)+"'" +
                " GROUP BY A_name  " +
                " ORDER BY avg(p_new_in_sw_line) DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getOLTListCalculateOnITVByCity")
    public List<Map<String, Object>> getOLTListCalculateOnITVByCity(
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT DISTINCT(olt_name), p_in_olt, p_new_in_olt  " +
                "FROM  olt_sw_bandwidth_liyonglv_forecast_all a  " +
                "  JOIN olt_info_all b  " +
                "  ON b.olt_ip = a.olt_ip  " +
                " WHERE p_new_in_olt_line>=0.6 AND a.city = '"+cityName.substring(0,2)+"'" +
                " ORDER BY p_new_in_olt DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


    @RequestMapping("/getSWListCalculateOnITVByCity")
    public List<Map<String, Object>> getSWListCalculateOnITVByCity    (
            @RequestParam(value = "city_name") String cityName
    ){
        String sql = "SELECT A_name, avg(p_in_sw) p_in_sw, avg(p_new_in_sw) p_new_in_sw  " +
                "FROM olt_sw_bandwidth_liyonglv_forecast_all a  " +
                "  JOIN SW_UP  " +
                "  ON sw_ip = A_IP  " +
                " WHERE p_new_in_sw >= 0.6 AND  a.city = '"+cityName.substring(0,2)+"'" +
                " GROUP BY A_name  " +
                " ORDER BY avg(p_new_in_sw) DESC ;";
        logger.info(sql);
        return jdbcTemplate.queryForList(sql);
    }


}
